create table GalacticUsers
(
	Id		int	not null identity(1,1),
	Username varchar(30) not null,	
	PasswordHash text not null,
	DomainUser text null,
	PushoverKey text null,
	Email text not null	
);

go

alter table GalacticUsers add constraint pk_galacticusers_id primary key(id) with fillfactor=90;
go

alter table GalacticUsers add constraint uq_galacticusers_username unique(Username);
go

create proc usp_AddGalacticUser
@Username varchar(30),
@PasswordHash text,
@Email text,
@DomainUser text = null,
@PushoverKey text = null
AS

	insert into GalacticUsers(Username, PasswordHash, Email, DomainUser, PushoverKey)
		values(@username, @PasswordHash, @Email, @DomainUser, @PushoverKey);

GO


create proc usp_GetUsers
AS

	SELECT
		Username,
		Email,
		PushoverKey,
		DomainUser

		from GalacticUsers

		order by Username ASC

GO


create proc usp_Login
@Username varchar(30),
@PasswordHash text
AS

	select 
		id,
		Username,
		Email,
		DomainUser,
		PushoverKey
	from GalacticUsers

	where username=@Username and PasswordHash like @PasswordHash;


GO


create table Config
(
	ConfigKey varchar(30) not null,
	ConfigValue varchar(128) not null
)
go

alter table Config add constraint uq_config_configkey unique(configkey);
go


create proc usp_GetConfigValue
@ConfigKey varchar(30)
AS

	select * from config where configkey = @ConfigKey;
	
GO

create proc usp_SetConfigValue
@ConfigKey varchar(30),
@ConfigValue varchar(128)
AS

	delete from Config where ConfigKey = @ConfigKey;

	insert into config(ConfigKey, ConfigValue) values(@ConfigKey, @ConfigValue);


GO

create proc usp_GetPushoverKey
@Username varchar(30)
AS

	select
		PushoverKey
		
		from GalacticUsers
		where Username = @Username

GO

create proc usp_GetUserEmail
@Username varchar(30)
AS

	select Email from GalacticUsers where username = @Username;

GO

create proc usp_GetUserNotificationSettings
@UserId int
AS

	select GalacticUsers.Email, GalacticUsers.PushoverKey from GalacticUsers
		where id=@UserId

GO


create table DataSets
(
	Id int not null identity(1,1),
	Name varchar(30) not null, 
	Loaded bit not null default(0),
	ParserRegex text not null
)
go

alter table DataSets add constraint pk_dataset_id primary key(Id);
alter table datasets add constraint uq_dataset_name unique(Name);
go

-- drop table data

create table Data
(
	Id bigint not null identity(1,1),
	DataSetId int not null,
	GalaxyName varchar(30) not null,
	ra float not null,
	decl float not null,
	Morphology varchar(30) not null,
	z float not null,
	Distance float null,	
	GeoPosition geometry
)
go

alter table Data add constraint pk_data_id primary key(id);
alter table data add constraint fk_data_dataset_datasetid foreign key(DataSetId) references DataSets(Id);
go

alter table DataSets add FileName text not null;
go


---

create proc usp_AddDataSet
@Name varchar(30),
@FileName text
AS

	INSERT INTO DataSets(Name, FileName) Values(@name, @FileName);
	select SCOPE_IDENTITY() AS Id;
GO


create proc usp_GetDataSets
AS

	SELECT ID, Name, Loaded from DataSets
	order by DataSets.Name ASC;
	
GO



alter table DataSets add Regex text null;
go
alter table DataSets add FieldMap text null;
go


create proc usp_SetParseSettings
@DataSetId int,
@Regex text,
@FieldMap text
AS

	update DataSets set regex=@Regex, FieldMap=@FieldMap 
		where Id = @DataSetId;

GO


create table DataSetLoadRequest
(
	id int not null identity(1,1),
	DataSetId int not null,
	UserId	int	not null,
	FilePath text not null,
	Processing bit not null default(0),
	Processed bit not null default(0),
	TotalRows bigint null,
	RowsProcessed bigint null,
	Email bit not null,
	Pushover bit not null
)
go

alter table DataSetLoadRequest add constraint pk_datasetloadrequest_id primary key(id);
alter table DataSetLoadRequest add constraint fk_datasetloadrequest_datasetid foreign key(datasetid) references DataSets(id);
alter table DataSetLoadRequest add constraint fk_datasetloadrequest_userid foreign key(UserId) references GalacticUsers(id);
go


alter table DataSetLoadRequest add StartedProcessing datetime null;
alter table DataSetLoadRequest add FinishedProcessing datetime null;
go


create proc usp_AddDataSetLoadRequst
@DataSetId int,
@UserName varchar(30),
@Email bit,
@Pushover bit
AS

	declare @userid int
	select @userid = Id from GalacticUsers where Username = @UserName;

	insert into DataSetLoadRequest(DataSetId, UserId, Email, Pushover)
		values(@DataSetId, @UserId, @email,	@Pushover);
	
	select SCOPE_IDENTITY() AS Id;

GO 

alter table DataSets add CommentMarkers varchar(30) null;
go

create proc usp_SetDataSetParameters
@DataSetId int,
@Regex text,
@FieldMap text,
@CommentMarkers varchar(30)
AS

	update DataSets set Regex = @Regex, FieldMap = @FieldMap, CommentMarkers = @CommentMarkers
		where Id = @DataSetId;

GO

create proc usp_GetDataSetLoadRequest
AS

	select top 1
	
	dslr.id, 
	dslr.DataSetId,
	dslr.UserId,
	dslr.Email,
	dslr.Pushover,
	ds.FileName,
	ds.Regex,
	ds.CommentMarkers,
	ds.FieldMap,
	gu.Email AS UserEmail,
	gu.PushoverKey,
	gu.Username

	 from DataSetLoadRequest as dslr

		inner join  DataSets AS ds on ds.id = dslr.DataSetId
		inner join GalacticUsers as gu on gu.id = dslr.userid

		where dslr.Processed = 0 and dslr.Processing = 0		
GO


create proc usp_UpdateDataSetLoadProgress
@id int,
@TotalRows bigint,
@RowsProcessed bigint
AS

	update DataSetLoadRequest set TotalRows = @TotalRows, RowsProcessed = @RowsProcessed, processing = 1
		where id = @id;

GO


create proc usp_StartDataLoadProgress
@id int,
@TotalRows bigint
AS

	update DataSetLoadRequest set TotalRows = @TotalRows, Processing = 1, RowsProcessed = 0, StartedProcessing = GETUTCDATE()

	where id = @id;

GO

create proc usp_GetDataSetLoadProgress
@id int
AS

	select 
		dslr.RowsProcessed,
		dslr.TotalRows		

	from DataSetLoadRequest as dslr

	where id = @id;

GO



create proc usp_AddData
@DataSetId int,
@DataSetLoadRequestId int,
@GalaxyName varchar(30),
@ra float,
@decl float,
@Morphology varchar(30),
@z float,
@Distance float
AS

	insert into data(DataSetId, GalaxyName, ra, decl, Morphology, z, Distance, GeoPosition)
		values(@DataSetId, @GalaxyName, @ra, @decl, @Morphology, @z, @distance, geometry::Point(radians(@decl), radians(@ra), 104001));


	update DataSetLoadRequest set RowsProcessed = RowsProcessed + 1 
		where id = @DataSetLoadRequestId;
Go

create proc usp_DataSetLoadComplete
@DataSetLoadRequestId int
AS

	update DataSetLoadRequest set Processed = 1, processing = 0, FinishedProcessing = getutcdate()
		where DataSetLoadRequest.id = @DataSetLoadRequestId;

	update DataSets set Loaded=1 WHERE datasets.id in (select DataSetId from DataSetLoadRequest where id=@DataSetLoadRequestId);

go



create proc usp_GetDataSetLoadProgress
@UserId int
AS

select 

   DataSetLoadRequest.id,
   DataSetLoadRequest.DataSetId,
   datasets.Name,
   DataSetLoadRequest.Processing,
   DataSetLoadRequest.Processed,
   DataSetLoadRequest.TotalRows,
   DataSetLoadRequest.RowsProcessed,
   DataSetLoadRequest.Email,
   DataSetLoadRequest.Pushover,
   DataSetLoadRequest.StartedProcessing,
   DataSetLoadRequest.FinishedProcessing,
   DataSetLoadRequest.UserId
   

  
   from DataSetLoadRequest


	 inner join datasets on datasets.id = datasetloadrequest.datasetid
	 inner join GalacticUsers on GalacticUsers.id = DataSetLoadRequest.UserId

	 where GalacticUsers.id = @UserId

	 AND 
	 (
		DataSetLoadRequest.Processed = 0
		or
		datediff(day, DataSetLoadRequest.FinishedProcessing, getutcdate()) < 1
	 )

	 order by 
	 
	 
	DataSetLoadRequest.DataSetId asc

GO



--------------------

create table DistanceCache
(
	ObjectOne bigint not null,
	ObjectTwo bigint not null,
	AngularDistance float null,
	ThreeDimensionDistance float null
);
go

alter table DistanceCache add constraint pk_DistanceCache_ObjectOne_ObjectTwo primary key (ObjectOne, ObjectTwo);
go

alter table DistanceCache add constraint fk_DistnaceCache_data_ObjectOne foreign Key(ObjectOne) references Data(id);
go
alter table DistanceCache add constraint fk_DistanceCache_data_ObjectTwo foreign Key(ObjectTwo) references Data(id);
go

